|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Using Cached Sequences for Primary Key Values
As shown in the preceding example, it can be efficient to use cached sequences to generate unique primary-key values. Not only is the performance of the cached sequence good, you are guaranteed a unique number (unless you have enabled CYCLE).
CAUTION: If you use cached sequences to generate primary-key values, be sure to set the NOCYCLE parameter for the sequence and make sure that the minimum and maximum values are sufficiently high. Cycling sequences causes integrity constraints to be violated.
Review of Sequences
Now you know how to create sequences and use them to generate unique values that can be used as primary keys. The Oracle sequence generator is a powerful tool that can be quite useful when needed. I recommend that you use the Oracle sequence generator to generate any sequential primary-key value.
By taking advantage of this feature, you can simplify application coding and improve performance. Performance is improved by reducing the manual locking and additional SQL statements that would be required to generate these unique values on your own. Although the sequence generator on its own may not be a huge performance improvement, in conjunction with many other optimizations, overall system performance benefits.
|
Join Performance
A join operation occurs when rows from two or more tables are combined in a single query. Typically, joins occur in the WHERE clause of SQL statements. The condition for the select in the WHERE clause is known as the join condition. There are several different types of joins, as shown in the following chart:
|
Join Type
| Description
|
|
Equijoin
| A join in which the pairs are joined with an equality condition. For example, the statement SELECT dogs.dogname, breeds.description FROM dogs, breeds where dogs.breed = breeds.breed; is an equijoin; it returns only those rows in which the value of dogs.breed is equal to breeds.breed.
|
Self join
| A join in which a table is joined to itself. The self join is similar to the equijoin except that the same table is used for both components.
|
Cartesian product
| The product of a join with no join condition. The result contains many rows. Unless you specifically need a Cartesian product, avoid this type of join. An example of a Cartesian product is the result of the SELECT * from dogs, breeds; query. A Cartesian product is usually the result of a mistake in a join statement and should be avoided.
|
Outer joins
| The result of an outer join is the rows that satisfy the join condition AND those rows in the first table for which no rows in the second table satisfy the join condition. For example, the statement SELECT dogs.dogname, breeds.description FROM dogs, breeds where dogs.breed = breeds.breed (+); is an outer join and returns only those rows in which the value of dogs.breed is equal to breeds.breed and all other rows in which the join condition is not met.
|
|
The following sections describe each of these types of joins, their tuning considerations, and ways of improving performance.
Equijoin
Equijoins occur when an equality operator is specified in the join condition. You can improve the performance of equijoins by specifying a join condition that can take advantage of indexes. If this join condition is frequently used, you have several options:
- Create indexes on the join condition columns. If indexes do not already exist, it will improve performance if the values of those columns are somewhat unique.
- Modify the join condition. It may be necessary to modify the join condition to take advantage of already-existing indexes.
- Create a cluster. If these tables meet the conditions of a cluster (as described in Chapter 26, Tuning SQL Statements), you will see significant benefits from clustering.
The benefit you see from using these options depends on the data and the application. If a majority of your operations on these tables are joins, you should seriously consider each of these options.
Self Join
A self join occurs when the WHERE clause in a SELECT statement references another column in the same table. The performance of a self join can be improved by specifying a join condition that takes advantage of indexes. If this join condition is frequently used, you have options similar to those available for the equijoin:
- Create indexes on the join condition columns. If indexes do not already exist, it will improve performance if the values of those columns are somewhat unique.
- Modify the join condition. It may be necessary to modify the join condition to take advantage of already-existing indexes.
Note that creating a cluster for a self join is not an option.
Cartesian Product
A Cartesian product is the product of a join with no join condition; the result contains many rows. Unless you specifically need a Cartesian product, avoid this type of join. In my experience, Cartesian products are rarely of much value. The best way to improve performance for a Cartesian product is to avoid using one.
Outer Join
The result of an outer join is the rows that satisfy the join condition AND those rows in the first table for which no rows in the second table satisfy the join condition. As with the equijoin operation, you can improve performance for an outer join by specifying a join condition that takes advantage of indexes. If this join condition is frequently used, you have several options:
- Create indexes on the join condition columns. If indexes do not already exist, it will improve performance if the values of those columns are somewhat unique.
- Modify the join condition. You may have to modify the join condition to take advantage of already-existing indexes.
- Create a cluster. If these tables meet the conditions of a cluster (as described in Chapter 26, Tuning SQL Statements), you can see significant benefits from clustering.
The benefit of using these options with an outer join depends on the data and the application. If a majority of your operations on these tables are joins, you should seriously consider each of these options.
|